﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using CRMFramework;

namespace CRMBiz
{
    public static class NoticeHandler
    {
        /// <summary>
        /// function to get notice list
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns>NoticeList</returns>
        public static DataSet GetNoticeList(int pageIndex, int pageSize)
        {
            DataSet ds;
            try
            {
                var sbSql = new StringBuilder("SELECT NoticeID,NoticeDescription,DATE_FORMAT(CreateDate,'%d-%b-%Y %h:%i %p') as CreateDate,DATE_FORMAT(EditDate,'%d-%b-%Y %h:%i %p') as Edit_Date,Operator,NoticeTitle,(select count(*) from crm_Notice) as Total from crm_Notice order by EditDate desc");
                sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                ds = DBhelper.ExecuteDataset(sbSql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        /// <summary>
        /// function to add new notice
        /// </summary>
        /// <param name="NoticeDescription"></param>
        /// <returns>New notice</returns>
        public static int AddNotice(string NoticeDescription, string CreateDate, string EditDate, string Operator, string NoticeTitle)
        {
            int intReturnValue = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@NoticeDescription", NoticeDescription),
                };

                string sbSql = "Select * From crm_Notice Where NoticeDescription = @NoticeDescription";
                DataSet ds = DBhelper.ExecuteDataset(sbSql, para);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    intReturnValue = 0;
                }
                else
                {
                    sbSql = "insert into crm_Notice(NoticeDescription,CreateDate,EditDate,Operator,NoticeTitle) values('" + NoticeDescription + "','" + CreateDate + "','" + EditDate + "','" + Operator + "','" + NoticeTitle + "')";
                    DBhelper.ExecuteNonQuery(sbSql, para);
                    intReturnValue = 1;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }



        /// <summary>
        /// function to Edit notice
        /// </summary>
        /// <param name="NoticeID"></param>
        /// <param name="NoticeDescription"></param>
        /// <returns>intReturnValue</returns>
        public static int EditNotice(int NoticeID, string NoticeDescription, string EditDate, string NoticeTitle)
        {
            int intReturnValue = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@NoticeID", NoticeID),
                    DBhelper.SetParameter("@NoticeDescription", NoticeDescription),
                    DBhelper.SetParameter("@EditDate", EditDate),
                };

                string sbSql = "Select * From crm_Notice Where NoticeID <> @NoticeID And  NoticeDescription = @NoticeDescription";
                DataSet ds = DBhelper.ExecuteDataset(sbSql, para);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    intReturnValue = 0;
                }
                else
                {
                    sbSql = "update crm_Notice set NoticeDescription ='"+NoticeDescription+"',EditDate='"+EditDate+"',NoticeTitle='"+NoticeTitle+"' where NoticeID = @NoticeID";
                    DBhelper.ExecuteNonQuery(sbSql, para);
                    intReturnValue = 1;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }



        /// <summary>
        /// function to Delete notice
        /// </summary>
        /// <param name="NoticeID"></param>   
        /// <returns>true/false</returns>
        public static bool DeleteNotice(int NoticeID)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@NoticeID", NoticeID),
                };

                string sbSql = "delete from crm_Notice where NoticeID=@NoticeID";
                DBhelper.ExecuteNonQuery(sbSql, para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }


        /// <summary>
        /// function to select recent notice
        /// </summary>
        /// <param name=""></param>   
        /// <returns>NoticeDescription</returns>
        public static DataSet ShowLatestNotice(int pageIndex, int pageSize)
        {
            DataSet ds;
            try
            {
                var sbSql = new StringBuilder("SELECT NoticeID,NoticeDescription,DATE_FORMAT(CreateDate,'%d-%b-%Y %h:%i %p') as CreateDate,DATE_FORMAT(EditDate,'%d-%b-%Y %h:%i %p') as Edit_Date,Operator,NoticeTitle,(select count(*) from crm_Notice) as Total from crm_Notice order by EditDate desc LIMIT 10");
                ds = DBhelper.ExecuteDataset(sbSql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }



    }
}
